﻿using System;
using System.Collections.Generic;

using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;

namespace ZhuGe.DAL.SqlServer
{
    /// <summary>
    /// 连接SQL Server 数据库类库
    /// </summary>
    public class SqlHelper
    {
        /// <summary>
        /// 数据库连接字符串；
        /// </summary>
        public static string ConnectionString ;// = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

        public static void SetConnect(string connStr)
        {
            ConnectionString = connStr;
        }

        /// <summary>
        /// 哈希表，存储参数；
        /// </summary>
        private static System.Collections.Hashtable parmCache = System.Collections.Hashtable.Synchronized(new Hashtable());


        /// <summary>
        /// 执行数据的相关操作，返回数据操作影响的行数
        /// </summary>
        /// <param name="cmdText">执行SQL命令的脚本</param>
        /// <param name="commandParameters">命令的参数</param>
        /// <returns>数据操作影响的行数</returns>
        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] commandParameters)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return result;
        }


        /// <summary>
        /// 执行数据的相关操作，返回数据操作影响的行数
        /// </summary>
        /// <param name="cmdText">执行SQL命令的脚本</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="commandParameters">命令的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                result = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return result;
        }


        /// <summary>
        /// 执行数据的相关操作，返回数据操作影响的行数
        /// </summary>
        /// <param name="connection">连接数据库的对象SqlConnection</param>
        /// <param name="cmdType">如何解释命令命令字符串 如：字符串；存储过程等</param>
        /// <param name="cmdText">执行SQL命令的脚本</param>
        /// <param name="commandParameters">命令的参数</param>
        /// <returns>返回数据操作影响的行数</returns>
        public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            int result = 0;
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            result = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            cmd.Dispose();

            return result;
        }


        /// <summary>
        /// 执行数据的相关操作，返回数据操作影响的行数
        /// </summary>
        /// <param name="trans">需要进行事务处理的对象SqlTransaction</param>
        /// <param name="cmdType">如何解释命令命令字符串 如：字符串；存储过程等</param>
        /// <param name="cmdText">执行SQL命令的脚本</param>
        /// <param name="commandParameters">命令的参数</param>
        /// <returns>返回数据操作影响的行数</returns>
        public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
            int result = cmd.ExecuteNonQuery();

            cmd.Parameters.Clear();
            cmd.Dispose();

            return result;
        }

        /// <summary>
        /// 执行SQL命令判断是否有记录
        /// </summary>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>真：有记录；假：无记录</returns>
        public static bool IsExistsRows(string cmdText, params SqlParameter[] commandParameters)
        {
            bool Result = false;

            using (SqlDataReader dr = ExecuteReader(cmdText, commandParameters))
            {
                if (dr.HasRows)
                    Result = true;
            }

            return Result;
        }


        /// <summary>
        /// 执行SQL命令返回数据读取器SqlDataReader
        /// </summary>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>数据读取器SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionString);

            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmd.Parameters.Clear();
            cmd.Dispose();

            return rdr;
        }

        /// <summary>
        /// 执行SQL命令返回数据读取器SqlDataReader
        /// </summary>
        /// <param name="cmdType">解释命令字符串类型</param>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>数据读取器SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            SqlConnection conn = new SqlConnection(ConnectionString);

            PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmd.Parameters.Clear();
            cmd.Dispose();

            return rdr;
        }


        /// <summary>
        /// 执行SQL命令返回数据读取器SqlDataReader
        /// </summary>
        /// <param name="cmdType">解释命令字符串类型</param>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>数据读取器SqlDataReader</returns>
        public static SqlDataReader ExecuteReader(SqlConnection conn, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
            SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            cmd.Parameters.Clear();
            cmd.Dispose();

            return rdr;
        }


        /// <summary>
        /// 执行SQL命令返回HashTable;用于读取数据字典;返回列表
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public static Hashtable ExecuteReader(string cmdText, string key, string value, params SqlParameter[] commandParameters)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlCommand cmd = new SqlCommand();

            try
            {
                Hashtable myResult = new Hashtable();

                PrepareCommand(cmd, conn, null, CommandType.Text, cmdText, commandParameters);
                using (SqlDataReader sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (sqldr.HasRows)
                    {
                        while (sqldr.Read())
                        {
                            myResult.Add(sqldr[key].ToString(), sqldr[value].ToString());
                        }
                        return myResult;
                    }
                    else
                    {
                        return null;
                    }
                }
            }
            catch
            {
                return null;
            }
        }


        /// <summary>
        /// 执行相关查询返回首行首列
        /// </summary>
        /// <param name="connectionString">连接数据库字符串</param>
        /// <param name="cmdType">解释命令字符串类型</param>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>object类型</returns>
        public static object ExecuteScalar(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            object result = null;

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
                result = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return result;
        }


        /// <summary>
        /// 执行相关查询，返回首行首列
        /// </summary>
        /// <param name="connection">连接数据库对象SqlConnection</param>
        /// <param name="cmdType">解释命令字符串类型</param>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>object类型</returns>
        public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();

            PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();

            cmd.Parameters.Clear();
            cmd.Dispose();

            return val;
        }


        /// <summary>
        /// 执行相关查询，返回首行首列
        /// </summary>
        /// <param name="connection">连接数据库对象SqlConnection</param>
        /// <param name="cmdType">解释命令字符串类型</param>
        /// <param name="cmdText">SQL命令</param>
        /// <param name="commandParameters">命令相关的参数</param>
        /// <returns>object类型</returns>
        public static object ExecuteScalar(string cmdText, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            object result = null;

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
                result = cmd.ExecuteScalar();

                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return result;
        }


        /// <summary>
        /// 高速缓存参数
        /// </summary>
        /// <param name="cacheKey"></param>
        /// <param name="commandParameters">参数</param>
        protected static void CacheParameters(string cacheKey, params SqlParameter[] commandParameters)
        {
            parmCache[cacheKey] = commandParameters;
        }


        /// <summary>
        /// 参数列表
        /// </summary>
        /// <param name="cacheKey">高速缓存关键字</param>
        /// <returns>参数</returns>
        private static SqlParameter[] GetCachedParameters(string cacheKey)
        {
            SqlParameter[] cachedParms = (SqlParameter[])parmCache[cacheKey];

            if (cachedParms == null)
                return null;

            SqlParameter[] clonedParms = new SqlParameter[cachedParms.Length];

            for (int i = 0, j = cachedParms.Length; i < j; i++)
                clonedParms[i] = (SqlParameter)((ICloneable)cachedParms[i]).Clone();

            return clonedParms;
        }


        /// <summary>
        /// 组合命令语句；
        /// </summary>
        /// <param name="cmd">SqlCommand</param>
        /// <param name="conn">SqlConnection</param>
        /// <param name="trans">SqlTransaction</param>
        /// <param name="cmdType">CommandType</param>
        /// <param name="cmdText">命令字符串</param>
        /// <param name="cmdParms">参数列表</param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        /// <summary>
        /// 组合命令语句；
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }


        /// <summary>
        /// 执行查询语句并返回数据集；
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string cmdText, string tableName, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
                SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
                sqlda.Fill(ds, tableName);
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return ds;
        }


        /// <summary>
        /// 执行查询语句并返回数据集；
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SqlConnection connection, string cmdText, string tableName, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();

            PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
            SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
            sqlda.Fill(ds, tableName);
            cmd.Parameters.Clear();
            cmd.Dispose();

            return ds;
        }

        /// <summary>
        /// 执行查询语句并返回数据集；
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string cmdText, CommandType comType, string tableName, params SqlParameter[] commandParameters)
        {
            SqlCommand cmd = new SqlCommand();
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                PrepareCommand(cmd, connection, null, comType, cmdText, commandParameters);
                SqlDataAdapter sqlda = new SqlDataAdapter(cmd);
                sqlda.Fill(ds, tableName);
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return ds;
        }

        /// <summary>
        /// 系统出错日志
        /// </summary>
        /// <param name="ex"></param>
        /// <param name="cmdText"></param>
        /// <param name="sqlpara"></param>
        public void ErrorLog(Exception ex, string cmdText, string sqlpara)
        {
            string mysql = "Insert Into Error_Log (ErrorCode,ErrorType,ErrorMsg,ErrorSQL,SQLPara) Values (@ErrorCode,@ErrorType,@ErrorMsg,@ErrorSQL,@SQLPara)";
            SqlConnection sqlconn = new SqlConnection(ConnectionString);
            SqlCommand sqlcomm = new SqlCommand();
            sqlcomm.Connection = sqlconn;
            sqlcomm.CommandText = mysql;
            sqlcomm.Parameters.Add("@ErrorCode", SqlDbType.NVarChar, 50).Value = ex.Source;
            sqlcomm.Parameters.Add("@ErrorType", SqlDbType.NVarChar, 200).Value = ex.TargetSite.ToString();
            sqlcomm.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 200).Value = ex.Message;
            sqlcomm.Parameters.Add("@ErrorSQL", SqlDbType.NVarChar, 400).Value = cmdText.Replace("'", "\"");
            sqlcomm.Parameters.Add("@SQLPara", SqlDbType.NVarChar, 400).Value = sqlpara.Replace("'", "\"");
            sqlconn.Open();
            sqlcomm.ExecuteNonQuery();
            sqlcomm.Dispose();
            sqlconn.Close();
            sqlconn.Dispose();
        }

        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public static bool TestConnect(string conStr)
        {
            try
            {
                SqlConnection connection = new SqlConnection(conStr);

                connection.Open();

                connection.Close();

                return true;
            }
            catch (Exception ex)
            {
                string str = ex.Message;
                return false;
                throw;
            }
        }
    }
}
